﻿using JESAI.Application.Abstractions.Dtos;
using JESAI.Application.Abstractions.Sys;
using JESAI.Application.Abstractions.Sys.Dtos;
using JESAI.Application.Abstractions.Sys.Querys;
using JESAI.AutoMapper.ObjectMapping;
using JESAI.Core.Attributes;
using JESAI.Core.Extensions;
using JESAI.DDD.Dtos;
using JESAI.Platform.Domain.Sys.Entities;
using JESAI.Platform.Domain.Sys.Repositories;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using JESAI.Core.Util.Extensions.Strings;
using JESAI.Core.Util.Helpers;
using JESAI.Core;
using JESAI.Uow;
using JESAI.Platform.Domain;
using SoEasyPlatform;
using JESAI.Core.Util;
using SqlSugar;
using System.Security.Cryptography;
using System.Text.RegularExpressions;
using JESAI.Biz.Data.Enums;

namespace JESAI.Application.Service.Sys
{
    [AppService]
    public class DataSourceService : IDataSourceService
    {
        private readonly IDataSourceTableRepository _dataSourceTableRepository;
        private readonly IDataSourceColumnsRepository _dataSourceColumnsRepository;
        private IUser _user;
        private readonly IUnitOfWorkManager _unitOfWorkManager;
        public DataSourceService(IDataSourceTableRepository dataSourceTableRepository, IDataSourceColumnsRepository dataSourceColumnsRepository, IUser user, IUnitOfWorkManager unitOfWorkManager)
        {
            _dataSourceTableRepository = dataSourceTableRepository;
            _dataSourceColumnsRepository = dataSourceColumnsRepository;
            _user = user;
            _unitOfWorkManager = unitOfWorkManager;
        }

        public async Task DelDataSourceTableById(string dataSourceTableId)
        {
            using (var uow = _unitOfWorkManager.CreateContext())
            {
                try
                {
                    await _dataSourceTableRepository.DeleteByIdAsync(dataSourceTableId);
                    await _dataSourceColumnsRepository.DeleteAsync(p => p.TableId == dataSourceTableId);
                    uow.Commit();
                }
                catch (Exception ex)
                {
                    uow.Rollback();
                    throw ex;
                }
            }
        }

        public async Task SyncDB(string dataSourceTableId)
        {
            var db = _unitOfWorkManager.Db;
            var tableList = _dataSourceTableRepository.GetList(p => p.Id == dataSourceTableId);
            List<EntitiesGen> genList = GetGenList(tableList, db, db.CurrentConnectionConfig.DbType);
            //foreach (var item in tableList)
            //{
            //    item.PropertyGens = item.PropertyGens.Where(it => it.IsIgnore == false).ToList();
            //    foreach (var property in item.PropertyGens)
            //    {
            //        if (property.IsSpecialType)
            //        {
            //            property.Type = "string";
            //        }
            //    }
            //}
            string key = TemplateHelper.EntityKey + SyntaxTreeHelper.TemplateString.GetHashCode();

            foreach (var item in genList)
            {
                var classString = TemplateHelper.GetTemplateValue(key, SyntaxTreeHelper.TemplateString, item);
                var type = SyntaxTreeHelper.GetModelTypeByClass(classString, item.ClassName);
                db.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
                {
                    EntityNameService = (type, info) =>
                    {
                        if (info.EntityName == item.ClassName || (info.EntityName == null && info.DbTableName == item.ClassName))
                        {
                            info.EntityName = item.ClassName;
                            info.DbTableName = item.TableName;
                            info.TableDescription = item.Description;
                        }
                    },
                    EntityService = (type, info) =>
                    {
                        if (info.EntityName == item.ClassName)
                        {
                            var column = item.PropertyGens.FirstOrDefault(it => it.PropertyName == info.PropertyName);
                            info.DbColumnName = column.DbColumnName;
                            info.ColumnDescription = column.Description;
                            info.IsNullable = column.IsNullable;
                            info.Length = Convert.ToInt32(column.Length);
                            info.DecimalDigits = Convert.ToInt32(column.DecimalDigits);
                            info.IsPrimarykey = column.IsPrimaryKey;
                            info.IsIdentity = column.IsIdentity;
                            info.IsIgnore = column.IsIgnore;
                            info.DataType = column.DbType;
                            if (db.CurrentConnectionConfig.DbType == DbType.Sqlite && info.IsIdentity)
                            {
                                info.DataType = "integer";
                            }
                        }
                    }
                };
                db.CodeFirst.InitTables(type);
            }
        }

        private List<EntitiesGen> GetGenList(List<DataSourceTableEntity> tableList, ISqlSugarClient db, SqlSugar.DbType databasedbType)
        {
            List<EntitiesGen> result = new List<EntitiesGen>();
            //var mapping = db.Queryable<MappingProperty>().ToList();
            //var tags = db.Queryable<TagProperty>().ToList();
            //if (databasedbType == DbType.MySql)
            //{
            //    var timestamp = types.FirstOrDefault(it => it.Name == "timestamp");
            //    if (timestamp != null)
            //    {
            //        timestamp.CSharepType = "DateTime";
            //    }
            //}
            foreach (var item in tableList)
            {
                var tableColumns = new List<DbColumnInfo> { };
                if (db.DbMaintenance.IsAnyTable(item.TableName, false))
                {
                    tableColumns = db.DbMaintenance.GetColumnInfosByTableName(item.TableName, false);
                }
                EntitiesGen gen = new EntitiesGen()
                {
                    ClassName = item.TableName,
                    Description = item.Remark,
                    TableName = item.TableName,
                    PropertyGens = new List<PropertyGen>()
                };

                var columns = _dataSourceColumnsRepository.GetList(it => it.TableId == item.Id);
                foreach (var column in columns)
                {
                    //var codeType = types.First(it => it.Name == column.CodeType);

                    //var dbType = GetTypeInfoByDatabaseType(codeType.DbType, databasedbType);
                    var dbColumnInfo = tableColumns.FirstOrDefault(it => it.DbColumnName.ToLower() == column.Columns.ToLower());
                    if (!string.IsNullOrEmpty(dbColumnInfo?.OracleDataType))
                    {
                        dbColumnInfo.DataType = dbColumnInfo.OracleDataType;
                    }
                    PropertyGen proGen = new PropertyGen()
                    {
                        DbColumnName = column.Columns,
                        Description = column.Name,
                        //IsIdentity = column.IsIdentity,
                        IsPrimaryKey = column.IsPrimaryKey,
                        PropertyName = column.Columns,//GetPropertyName(column.ClassProperName),
                        Type = GetDataType(column.DataType), //IsSpecialType(column) ? GetType(column) : codeType.CSharepType,
                        IsNullable = column.IsAllowNull == false,
                        //DbType = dbType.Name,
                        //Length = dbType.Length,
                        Db_Length = dbColumnInfo?.Length,
                        Db_DecimalDigits = dbColumnInfo?.DecimalDigits,
                        Db_DateType = dbColumnInfo?.DataType,
                        //DecimalDigits = dbType.DecimalDigits,
                        //IsSpecialType = IsSpecialType(column),
                        //CodeType = column.CodeType,
                        //DefaultValue = column.DefaultValue
                    };
                    //var mappings = mapping.Where(it => item.DbId == it.DbId && it.TableName == item.ClassName && it.ColumnName == column.ClassProperName).ToList(); ;
                    //proGen.MappingProperties = tags.Where(it => mappings.Any(x => x.TagId == it.Id + "")).ToList();
                    gen.PropertyGens.Add(proGen);

                }
                result.Add(gen);
            }
            return result;
        }
        private static bool IsSpecialType(CodeColumns column)
        {
            return Regex.IsMatch(column.ClassProperName, @"\[.+\]");
        }

        private static string GetDataType(DataSourceDataType dbType)
        {
            switch (dbType)
            {
                case DataSourceDataType.String:
                    return "varchar";
                case DataSourceDataType.Integer:
                    return "int";
                default:
                    return "string";
            }
        }

        private static string GetType(CodeColumns column)
        {
            string type = "string";
            if (IsSpecialType(column))
            {
                type = Regex.Match(column.ClassProperName, @"\[(.+)\]").Groups[1].Value;
            }

            return type;
        }

        private static string GetPropertyName(string name)
        {
            return Regex.Replace(name, @"\[(.+)\]", "");
        }

        private DbTypeInfo GetTypeInfoByDatabaseType(DbTypeInfo[] dbType, DbType databasedbType)
        {
            DbTypeInfo result = dbType.First();
            List<string> mstypes = new List<string>();
            switch (databasedbType)
            {
                case DbType.MySql:
                    mstypes = SqlSugar.MySqlDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.SqlServer:
                    mstypes = SqlSugar.SqlServerDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    mstypes.Add("xml");
                    break;
                case DbType.Sqlite:
                    mstypes = SqlSugar.SqliteDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Oracle:
                    mstypes = SqlSugar.OracleDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.PostgreSQL:
                    if (dbType.Any(it => it.Name == "blob"))
                    {
                        var list = new List<DbTypeInfo> { new DbTypeInfo() { Name = "bit" } };
                        list.AddRange(dbType);
                        dbType = list.ToArray();
                    }
                    mstypes = SqlSugar.PostgreSQLDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Dm:
                    mstypes = SqlSugar.DmDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Kdbndp:
                    mstypes = SqlSugar.KdbndpDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                default:
                    break;
            }
            result = dbType.FirstOrDefault(it => mstypes.Contains(it.Name.ToLower()));
            if (result == null)
            {
                throw new Exception("暂时不支持类型" + string.Join(",", dbType.Select(it => it.Name)));
            }
            return result;
        }
        public async Task DelDataSourceTableByIds(string[] dataSourceTableIds)
        {
            using (var uow = _unitOfWorkManager.CreateContext())
            {
                try
                {
                    await _dataSourceTableRepository.DeleteByIdsAsync(dataSourceTableIds);
                    await _dataSourceColumnsRepository.DeleteAsync(p => dataSourceTableIds.Contains(p.TableId));
                    uow.Commit();
                }
                catch (Exception ex)
                {
                    uow.Rollback();
                    throw ex;
                }
            }
        }

        public async Task<PagedDto<DataSourceTableDto>> GetPageList(DataSourceTablePageQuery datasourceTableQuery)
        {
            var totalNumber = 0;
            var data = _dataSourceTableRepository._DbQueryable
                 .WhereIF(!datasourceTableQuery.TableName.IsEmpty(), it => it.TableName.Contains(datasourceTableQuery.TableName))
                   .WhereIF(!datasourceTableQuery.TableCode.IsEmpty(), it => it.TableCode.Contains(datasourceTableQuery.TableCode))
                   .WhereIF(!datasourceTableQuery.TableComments.IsEmpty(), it => it.TableComments.Contains(datasourceTableQuery.TableComments))
                    .ToPageList(datasourceTableQuery.PageIndex, datasourceTableQuery.PageSize, ref totalNumber);
            return new PagedDto<DataSourceTableDto>(totalNumber, data.MapTo<List<DataSourceTableDto>>());
        }

        public async Task SaveOrUpdateDataSource(DataSourceTableInput dataSourceTableInput, List<DataSourceColumnInput> dataSourceColumnInputs)
        {
            using (var uow = _unitOfWorkManager.CreateContext())
            {
                try
                {
                    DataSourceTableEntity dataSourceTableModel = await SaveOrUpdateDataSourceTableAsync(dataSourceTableInput);
                    await SaveOrUpdateDataSourceAsync(dataSourceTableModel, dataSourceColumnInputs);
                    uow.Commit();
                }
                catch (Exception ex)
                {
                    uow.Rollback();
                    throw ex;
                }
            }
        }

        private async Task SaveOrUpdateDataSourceAsync(DataSourceTableEntity dataSourceTableModel, List<DataSourceColumnInput> dataSourceColumnInputs)
        {
            var dataSourceColumns = dataSourceColumnInputs.MapTo<List<DataSourceColumnsEntity>>();
            var updateDataSourceColumns = dataSourceColumns.Where(it => it.Id.IsNotNullOrEmpty()).ToList();
            var insertDataSourceColumns = dataSourceColumns.Where(it => it.Id.IsNullOrEmptyOrWhiteSpace()).ToList();
            if (updateDataSourceColumns.Any())
            {
                var updateColumns = _dataSourceColumnsRepository._DbQueryable.Where(it => updateDataSourceColumns.Select(c => c.Id).Contains(it.Id)).ToList();
                updateDataSourceColumns.ForEach(it =>
                {
                    var column = updateColumns.FirstOrDefault(c => c.Id == it.Id);
                    if (column == null)
                    {
                        return;
                    }
                    it.TableId = dataSourceTableModel.Id;
                    it.TableCode = dataSourceTableModel.TableCode;
                    it.CreateTime = column.CreateTime;
                    it.Cid = column.Cid;
                    it.Uid = _user.Id.ToLong();
                    it.UpdateTime = DateTime.Now;
                });
                var updateColumnIds = updateDataSourceColumns.Select(p => p.Id).ToArray();
                var delColumnIds = _dataSourceColumnsRepository._DbQueryable.Where(it => !SqlFunc.ContainsArray(updateColumnIds, it.Id)).Select(it => it.Id).ToArray();

                if (delColumnIds.Any())
                {
                    await _dataSourceColumnsRepository.DeleteByIdsAsync(delColumnIds);
                }
                await _dataSourceColumnsRepository.UpdateRangeAsync(updateDataSourceColumns);
            }
            if (insertDataSourceColumns.Any())
            {
                insertDataSourceColumns.ForEach(it =>
                {
                    it.Id = Guid.NewGuid().ToString();
                    it.TableId = dataSourceTableModel.Id;
                    it.CreateTime = DateTime.Now;
                    it.TableCode = dataSourceTableModel.TableCode;
                    it.TableId = dataSourceTableModel.Id;
                    it.Cid = _user.Id.ToLong();
                });
                await _dataSourceColumnsRepository.InsertRangeAsync(insertDataSourceColumns);

            }
        }

        public async Task SaveOrUpdateDataSourceTable(DataSourceTableInput dataSourceTableInput)
        {
            await SaveOrUpdateDataSourceTableAsync(dataSourceTableInput);
        }

        private async Task<DataSourceTableEntity> SaveOrUpdateDataSourceTableAsync(DataSourceTableInput dataSourceTableInput)
        {
            DataSourceTableEntity dataSourceTableModel = null;
            if (dataSourceTableInput.Id.IsNullOrWhiteSpace())
            {
                dataSourceTableModel = dataSourceTableInput.MapTo<DataSourceTableEntity>();
                dataSourceTableModel.Id = Guid.NewGuid().ToString();
                if (dataSourceTableModel.TableCode.IsNullOrWhiteSpace())
                {
                    dataSourceTableModel.TableCode = Guid.NewGuid().ToString();
                }
                dataSourceTableModel.CreateTime = DateTime.Now;
                dataSourceTableModel.Cid = _user.Id.ToInt();
                await _dataSourceTableRepository.InsertAsync(dataSourceTableModel);
            }
            else
            {
                var model = await _dataSourceTableRepository.GetByIdAsync(dataSourceTableInput.Id);
                dataSourceTableInput.MapTo(model);
                model.Uid = _user.Id.ToInt();
                model.UpdateTime = DateTime.Now;
                dataSourceTableModel = model;
                await _dataSourceTableRepository.UpdateAsync(model);
            }
            return dataSourceTableModel;
        }

        public async Task<List<DataSourceColumnDto>> QueryDataSourceColumnsByTableId(string dataSourceTableId)
        {
            var dataSourceColumns = _dataSourceColumnsRepository._DbQueryable.Where(it => it.TableId == dataSourceTableId).ToList();
            return dataSourceColumns.MapTo<List<DataSourceColumnDto>>();
        }

        public async Task<List<DataSourceTableDto>> GetAllDataSourceTables()
        {
            var dataSourceTables = _dataSourceTableRepository._DbQueryable.ToList();
            return dataSourceTables.MapTo<List<DataSourceTableDto>>();
        }

        public async Task<List<DataSourceColumnDto>> GetDataSourceColumnsByTableId(string tableId)
        {
            var dataSourceColumns = _dataSourceColumnsRepository._DbQueryable.Where(it => it.TableId == tableId).ToList();
            return dataSourceColumns.MapTo<List<DataSourceColumnDto>>();
        }
    }
}
